package com.ztc.ZtcClassesPrice.dao;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import java.util.Map;
import java.util.UUID;

import org.hibernate.Session;
import org.hibernate.jdbc.Work;
import org.springframework.stereotype.Repository;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.aqgj.util.BaseDaoImpl;
import com.ztc.ZtcClassesPrice.bean.ZtcClassesPrice;
/**
 * <p>
 *
 * </p>
 *
 * @ClassName ZtcClassesPriceDao
 * @JDK 1.5
 * @author
 * @date
 */
@Repository("ZtcClassesPriceDao")
public class ZtcClassesPriceDao extends BaseDaoImpl<ZtcClassesPrice> {
	public String batchInsertPrice(String id, JSONArray priceArray, Map<Integer, String> siteMap){
		Session session = getSessionFactory().openSession();
		session.doWork(new Work(){
			@Override
			public void execute(Connection connection) throws SQLException {
				// TODO Auto-generated method stub
				String insertSql="insert into ztc_classes_price(id,distance, price, fk_start_classes_site_id, fk_end_classes_site_id, fk_ztc_classes_id)"
		                +"values(?,?,?,?,?,?)";
				PreparedStatement stmt= connection.prepareStatement(insertSql);
				connection.setAutoCommit(false);
				JSONObject object;
				for (int i = 0; i < priceArray.size(); i++) {
					object = priceArray.getJSONObject(i);
					if (object == null || object.isEmpty()) {
						continue;
					}
					stmt.setString(1,UUID.randomUUID().toString().replace("-", ""));
					stmt.setString(2,object.getString("distance"));
					stmt.setString(3,object.getString("price"));
					stmt.setString(4,siteMap.get(object.getInteger("col")));
					stmt.setString(5,siteMap.get(object.getInteger("row")));
					stmt.setString(6,id);
					stmt.addBatch();
//					if(i % 200 == 0){
//						stmt.executeBatch();
//						connection.commit();
//					}
				}
				stmt.executeBatch();
				connection.commit();
			}
		});
		session.close();
		return "1";
	}
    public String save(ZtcClassesPrice ztcPrice) {
    	int insertResult=0;
		Connection  conn=null;
		Statement stmt=null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
		    conn = DriverManager.getConnection("jdbc:mysql://192.168.0.253:3306/an168?useUnicode=true&characterEncoding=utf8", "root", "root");
			stmt = conn.createStatement();
			String id=UUID.randomUUID().toString().replace("-", "");
		    String insertSql="insert into ztc_classes_price(id,distance, price, fk_start_classes_site_id, fk_end_classes_site_id, fk_ztc_classes_id)"
	                +"values('"+id+"','"+ztcPrice.getDistance()+"','"+ztcPrice.getPrice()+"','"+ztcPrice.getFkStartClassesSiteId()+"','"+ztcPrice.getFkEndClassesSiteId()+"','"+ztcPrice.getFkZtcClassesId()+"')";
		    System.out.println(insertSql);
		    insertResult=stmt.executeUpdate(insertSql);
		}catch (ClassNotFoundException e) {
			e.printStackTrace();
		}catch (SQLException e) {
			e.printStackTrace();
		}finally{
			if(stmt!=null){
				try {
					stmt.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			if(conn!=null){
				try {
					conn.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
		return String.valueOf(insertResult);
       /* this.getHibernateTemplate().save(ztcPrice);
        return ztcPrice.getId();*/
    }
    public List<ZtcClassesPrice> getAllZtcClassesPrices() {
        return this.getAll();
    }

    public String delete(String id,String classesId,String modifyUserId) {
    	String sql="delete from ztc_classes_price where 1=1";
    	if(id!=null){
    		sql+=" and id='"+id+"'";
    	}
    	if(classesId!=null){
    		sql+=" and fk_ztc_classes_id='"+classesId+"'";
    	}
    	if(modifyUserId!=null){
    		sql+=" and fk_modify_user_id='"+modifyUserId+"'";
    	}
    	if(this.currentSession().createNativeQuery(sql).executeUpdate()>=0){
			return "1";
		}
		return "0";
    }
    
    public List<ZtcClassesPrice> searchInfoEquals(ZtcClassesPrice ztcClassesPrice) {
        String sql="select * from ztc_classes_price where 1=1";
        if(ztcClassesPrice.getPrice()!=null){
        	sql+=" and price='"+ztcClassesPrice.getPrice()+"'";
        }
        if(ztcClassesPrice.getDistance()!=null){
        	sql+=" and distance='"+ztcClassesPrice.getDistance()+"'";
        }
        if(ztcClassesPrice.getFkStartClassesSiteId()!=null){
        	sql+=" and fk_start_classes_site_id='"+ztcClassesPrice.getFkStartClassesSiteId()+"'";
        }
        if(ztcClassesPrice.getFkEndClassesSiteId()!=null){
        	sql+=" and fk_end_classes_site_id='"+ztcClassesPrice.getFkEndClassesSiteId()+"'";
        }
        if(ztcClassesPrice.getFkZtcClassesId()!=null){
        	sql+=" and fk_ztc_classes_id='"+ztcClassesPrice.getFkZtcClassesId()+"'";
        }
        if(ztcClassesPrice.getId()!=null){
        	sql+=" and id='"+ztcClassesPrice.getId()+"'";
        }
        List<ZtcClassesPrice> list=this.currentSession().createNativeQuery(sql,ZtcClassesPrice.class).getResultList();
        return list;
    }
    
    public List<ZtcClassesPrice> searchInfoLikes(String startSiteName,String endSiteName) {
        String sql="select * from ztc_classes_price as price,ztc_classes_site as start_site,ztc_classes_site as end_site"
                   +" where price.fk_start_classes_site_id=start_site.id and price.fk_end_classes_site_id=end_site.id";
        if(startSiteName!=null){
        	sql+=" and start_site.name like '%"+startSiteName+"%'";
        }
        if(endSiteName!=null){
        	sql+=" and end_site.name like '%"+endSiteName+"%'";
        }
        List<ZtcClassesPrice> list=this.currentSession().createNativeQuery(sql,ZtcClassesPrice.class).getResultList();
        return list;
    }
    
    public String update(ZtcClassesPrice ztcClassesPrice) {
        this.getHibernateTemplate().saveOrUpdate(ztcClassesPrice);
        if(ztcClassesPrice.getId()!=null){
        	return "1";
        }
        return "0";
    }
}